package com.ruoyi.web.controller.project;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.ruoyi.common.core.domain.R;
import com.ruoyi.common.core.page.TableDataInfo;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.project.domain.entity.*;
import com.ruoyi.project.domain.query.ProjectCompanyQuery;
import com.ruoyi.project.domain.query.ProjectQuery;
import com.ruoyi.project.mapper.ProjectCompanyMapper;
import com.ruoyi.project.mapper.ProjectMapper;
import com.ruoyi.project.mapper.ProjectSaleMapper;
import com.ruoyi.project.mapper.ProjectStoreMapper;
import com.ruoyi.project.service.*;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.hpsf.Decimal;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

@RestController
@RequestMapping("/project/company")
@Api(tags = "项目信息", description = "项目基本信息")
@Component("syncDatas")
public class ProjectCompanyController {

    // 数据库连接信息
    private static final String DB_URL_HC2024 = "jdbc:sqlserver://172.30.30.30:1433;databaseName=HC2024;";
    private static final String DB_URL_HC2022 = "jdbc:sqlserver://172.30.30.30:1433;databaseName=HC2022;";
    private static final String DB_URL_XXWL2018 = "jdbc:sqlserver://172.30.30.30:1433;databaseName=XXWL2018;";
    private static final String DB_URL_XXWL = "jdbc:sqlserver://172.30.30.30:1433;databaseName=XXWL;";
    private static final String USER_NAME = "sa";
    private static final String USER_PWD = "NBhcit-2020";

    // 定义 DECIMAL(10, 2) 的最大值和最小值
    private static final BigDecimal MAX_VALUE = new BigDecimal("99999999.99");
    private static final BigDecimal MIN_VALUE = new BigDecimal("-99999999.99");

    @Resource
    private IProjectCompanyService iProjectCompanyService;
    @Resource
    private IProjectFinanceService financeService;


    @Resource
    private IProjectGoodsService goodsService;

    @Resource
    private IProjectStoreService projectStoreService;

    @Resource
    private IProjectSaleService projectSaleService;

    @Resource
    IProjectFeeService feeService;

    @Resource
    IProjectGjpUserService projectGjpUserService;

    @Resource
    IProjectService projectService;

    @Resource
    IProjectPhaseService phaseService;


    @Autowired
    private ProjectCompanyMapper projectCompanyMapper;
    @Autowired
    private ProjectStoreMapper projectStoreMapper;
    @Autowired
    private ProjectSaleMapper projectSaleMapper;



    private final String DB_URL = "jdbc:mysql://172.30.30.88:3306/hcitx_performance?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8";
    private final String DB_USER = "root";
    private final String DB_PASSWORD = "hcit@2024";
    private final String OUTPUT_FILE = "/home/ruoyi/mysqlBackup/db_backup_" + System.currentTimeMillis() + ".sql";
    @Autowired
    private ProjectMapper projectMapper;

    @Resource
    private IProjectMatchingService matchingService;

    //执行一次创建配套费用
    @GetMapping(value = "/createMatching")
    public void createMatching() {
        List<Project> list = projectMapper.selectList(null);
        for (Project project : list) {
            matchingService.initMatching(project.getProjectNo());
        }
    }


    //定时备份数据库
    @GetMapping(value = "/backupMysql")
    public void backupDatabase() {
        try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            List<String> tables = getTableNames(connection);
            BufferedWriter writer = new BufferedWriter(new FileWriter(OUTPUT_FILE));

            // 导出每个表的结构和数据
            for (String table : tables) {
                exportCreateTableStatement(connection, writer, table);
                exportTableData(connection, writer, table);
            }

            writer.close();
            System.out.println("✅ 备份完成，文件路径：" + OUTPUT_FILE);

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("❌ 备份失败：" + e.getMessage());
        }
    }

    private List<String> getTableNames(Connection conn) throws SQLException {
        List<String> tables = new ArrayList<>();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SHOW TABLES");

        while (rs.next()) {
            tables.add(rs.getString(1));
        }

        return tables;
    }

    private void exportCreateTableStatement(Connection conn, BufferedWriter writer, String table) throws SQLException, IOException {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SHOW CREATE TABLE " + table);
        if (rs.next()) {
            String createTableSql = rs.getString(2);
            writer.write("-- Table structure for table `" + table + "`\n");
            writer.write(createTableSql + ";\n\n");
        }
    }

    private void exportTableData(Connection conn, BufferedWriter writer, String table) throws SQLException, IOException {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM " + table);

        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();

        writer.write("-- Dumping data for table `" + table + "`\n");

        while (rs.next()) {
            StringBuilder insert = new StringBuilder("INSERT INTO `" + table + "` VALUES (");

            for (int i = 1; i <= columnCount; i++) {
                Object value = rs.getObject(i);
                if (value == null) {
                    insert.append("NULL");
                } else if (value instanceof String || value instanceof java.sql.Date || value instanceof java.sql.Timestamp) {
                    insert.append("'").append(value.toString().replace("'", "''")).append("'");
                } else {
                    insert.append(value);
                }

                if (i < columnCount) {
                    insert.append(", ");
                }
            }

            insert.append(");\n");
            writer.write(insert.toString());
        }

        writer.write("\n");
    }



    @GetMapping(value = "/list")
    public R list() {
        return R.ok(iProjectCompanyService.getList());
    }


    @GetMapping(value = "/listByName")
    public R listByName(ProjectCompanyQuery query) {
        LambdaQueryWrapper<ProjectCompany> wrapper = new QueryWrapper<ProjectCompany>().lambda();
        wrapper.like(query.getCompanyName() != null, ProjectCompany::getCompanyName, query.getCompanyName());
        if (StringUtils.isEmpty(query.getCompanyName())) {
            return R.ok(new ArrayList<ProjectCompany>());
        }
        return R.ok(iProjectCompanyService.list(wrapper));

    }

    @GetMapping(value = "/finance")
    public R finance() {
        return R.ok(financeService.getList());
    }


    @GetMapping(value = "/complete")
    public void complete() {
       //定时查询有没有项目可以归档的
        //查询待归档的
        LambdaQueryWrapper<ProjectPhase> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(ProjectPhase::getPhaseId,"7");
        queryWrapper.eq(ProjectPhase::getStatus,"1");
        List<ProjectPhase> phaseList = phaseService.list(queryWrapper);
        for (ProjectPhase projectPhase : phaseList) {
            boolean isComplete = phaseService.projectCompelete(projectPhase);
            if (isComplete) {
                projectPhase.setStatus("2");//设置为已完成
                phaseService.updateById(projectPhase);
            }
        }
    }

    //同步管家婆项目数据
    @GetMapping(value = "/syncCompany")
    public void syncCompany() {
        final int BATCH_SIZE = 1000; // 每批处理1000条记录
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 获取连接
            connection = getConnectionHC2024();
            // SQL 查询语句
            String sql = "SELECT btypeid,bfullname,bname,Telandaddress ,Area FROM btype";
            // 创建 PreparedStatement 对象
            preparedStatement = connection.prepareStatement(sql);
            // 执行查询
            resultSet = preparedStatement.executeQuery();
            // 处理结果集
            List<ProjectCompany> list = new ArrayList<>();
            int totalProcessed = 0;
            while (resultSet.next()) {
                ProjectCompany company = new ProjectCompany();
                String companyName = "";
                String id = resultSet.getString("btypeid");
                String fullName = resultSet.getString("bfullname");
                String simpleName = resultSet.getString("bname");
                String contacts = resultSet.getString("Telandaddress");
                String area = resultSet.getString("Area");
                companyName = fullName
                        + (simpleName != null && !simpleName.isEmpty() ? "-" + simpleName : "")
                        + (contacts != null && !contacts.isEmpty() ? "-" + contacts : "");
                company.setId(id);
                company.setCompanyName(companyName);
                company.setFullName(fullName);
                company.setArea(area);
                company.setContacts(contacts);
                company.setSimpleName(simpleName);
                list.add(company);
                // 输出数据
                System.out.println("store " + company);

                // 达到批次大小时异步提交
                if (list.size() >= BATCH_SIZE) {
                    iProjectCompanyService.syncData(new ArrayList<>(list));
                    totalProcessed += list.size();
                    list.clear();
                    System.out.println("已处理: " + totalProcessed + " 条记录");
                }
            }
            //更新或插入
//                iProjectCompanyService.syncData(list);
            // 处理剩余记录
            if (!list.isEmpty()) {
                iProjectCompanyService.syncData(list);
                totalProcessed += list.size();
                System.out.println("总共处理: " + totalProcessed + " 条记录");
            }
        } catch (SQLException e) {
            System.err.println("读取表 btype 时出错！");
            e.printStackTrace();
        } finally {
            // 关闭资源
            closeResources(connection, preparedStatement, resultSet);
        }
    }

    //同步发票数据
    @GetMapping(value = "/syncFinance")
    public void syncFinance() {
        final int BATCH_SIZE = 1000; // 每批处理1000条记录
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 获取连接
            connection = getConnectionHC2024() ;

            // SQL 查询语句
            String sql = "SELECT Vchcode, Xw_Dsf1,Vchcode,DlyOrder FROM xw_detaildsf";

            // 创建 PreparedStatement 对象
            preparedStatement = connection.prepareStatement(sql);

            // 执行查询
            resultSet = preparedStatement.executeQuery();
            // 处理结果集
            List<ProjectFfinance> list = new ArrayList<>();
            int totalProcessed = 0;
            financeService.deleteAll("2024");
            while (resultSet.next()) {
                ProjectFfinance finance = new ProjectFfinance();
                String department = resultSet.getString("Xw_Dsf1");
                finance.setFinance(department);
                finance.setCode(String.valueOf(resultSet.getInt("Vchcode")));
                finance.setOrders(String.valueOf(resultSet.getInt("DlyOrder")));
                finance.setDatas("2024");
                list.add(finance);
                // 达到批次大小时异步提交
                if (list.size() >= BATCH_SIZE) {
                    financeService.syncData(new ArrayList<>(list));
                    totalProcessed += list.size();
                    list.clear();
                    System.out.println("已处理: " + totalProcessed + " 条记录");
                }
            }
//                financeService.syncData(list);
            // 处理剩余记录
            if (!list.isEmpty()) {
                financeService.syncData(list);
                totalProcessed += list.size();
                System.out.println("总共处理: " + totalProcessed + " 条记录");
            }
        } catch (SQLException e) {
            System.err.println("读取表 btype 时出错！");
            e.printStackTrace();
        } finally {
            // 关闭资源
            closeResources(connection, preparedStatement, resultSet);
        }
    }

    //同步发票数据
    @GetMapping(value = "/syncFinance2022")
    public void syncFinance2022() {
        final int BATCH_SIZE = 1000; // 每批处理1000条记录
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 获取连接
            connection = getConnectionHC2022() ;

            // SQL 查询语句
            String sql = "SELECT Vchcode, Xw_Dsf1,DlyOrder FROM xw_detaildsf";

            // 创建 PreparedStatement 对象
            preparedStatement = connection.prepareStatement(sql);

            // 执行查询
            resultSet = preparedStatement.executeQuery();
            // 处理结果集
            List<ProjectFfinance> list = new ArrayList<>();
            int totalProcessed = 0;
            financeService.deleteAll("2022");
            while (resultSet.next()) {
                ProjectFfinance finance = new ProjectFfinance();
                String department = resultSet.getString("Xw_Dsf1");
                finance.setFinance(department);
                finance.setCode(String.valueOf(resultSet.getInt("Vchcode")));
                finance.setOrders(String.valueOf(resultSet.getInt("DlyOrder")));
                finance.setDatas("2022");
                list.add(finance);
                // 达到批次大小时异步提交
                if (list.size() >= BATCH_SIZE) {
                    financeService.syncData(new ArrayList<>(list));
                    totalProcessed += list.size();
                    list.clear();
                    System.out.println("已处理: " + totalProcessed + " 条记录");
                }
            }
//                financeService.syncData(list);
            // 处理剩余记录
            if (!list.isEmpty()) {
                financeService.syncData(list);
                totalProcessed += list.size();
                System.out.println("总共处理: " + totalProcessed + " 条记录");
            }
        } catch (SQLException e) {
            System.err.println("读取表 btype 时出错！");
            e.printStackTrace();
        } finally {
            // 关闭资源
            closeResources(connection, preparedStatement, resultSet);
        }
    }



    //更新调平的类型
    @GetMapping(value = "/updateBalance")
    public void updateBalance(){
        LambdaQueryWrapper<ProjectFee> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.like(ProjectFee::getRemark,"专用调整工程材料的费用单");
        List<ProjectFee> fees = feeService.list(queryWrapper);
        for (ProjectFee fee : fees) {
            fee.setMainType("666");
        }
        feeService.updateBatchById(fees);
    }


    //同步报销费用汇总
    @GetMapping(value = "/syncFee")
    public void syncFee() {
        final int BATCH_SIZE = 1000; // 每批处理1000条记录
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 获取连接  这里还要取其他数据库的  不只是存在2024中
            connection = getConnectionHC2024() ;

            // SQL 查询语句
            //全量更新?


            // 计算“前三天”（即 3 天前）
//            LocalDate threeDaysAgo = LocalDate.now().minusDays(3); // 例如：2025-09-27
            // 计算“一年前”
//            LocalDate threeDaysAgo = LocalDate.now().minusYears(1); // 例如：2025-11-27 → 2024-11-27
// 计算“半年前”
            LocalDate threeDaysAgo = LocalDate.now().minusMonths(6); // 例如：2025-11-27 → 2025-05-27
            /** 类型主类  根据数据观察  vcType是36 且 useType是1的表示是报销费用  vcType是4 类型是1的为收款    以上取大于0的*/

//            String sql = "SELECT btypeid, vchtype, total, [date], usedtype, etypeid, comment " +
//                    "FROM dlya " +
//                    "WHERE [date] >= '" + threeDaysAgo + "'";


            String sql = "SELECT btypeid, vchtype, total, [date], usedtype, etypeid, comment " +
                    "FROM dlya " ;

//            String sql = "select btypeid,vchtype,total,[date],usedtype,etypeid,comment from dlya ";


            // 创建 PreparedStatement 对象
            preparedStatement = connection.prepareStatement(sql);

            // 执行查询
            resultSet = preparedStatement.executeQuery();
            // 处理结果集
            List<ProjectFee> list = new ArrayList<>();
            //删除的时候也需要同步删除？
            feeService.deleteAll(threeDaysAgo );//删除历史记录 只需要删除2024的
            int totalProcessed = 0;
            while (resultSet.next()) {
                ProjectFee fee = new ProjectFee();
                String housekeeperProject = resultSet.getString("btypeid");
                fee.setHousekeeperProject(housekeeperProject);
                if (StringUtils.isEmpty(resultSet.getString("usedtype"))){//是空的默认设置一个值
                    fee.setUseType("999");
                }else {
                    fee.setUseType(resultSet.getString("usedtype"));
                }

                fee.setMainType(resultSet.getString("vchtype"));
                fee.setDatas("2024");
                fee.setTotal(resultSet.getBigDecimal("total"));
                fee.setSubmitDate(resultSet.getDate("date"));
                fee.setUserId(resultSet.getString("etypeid"));
                fee.setRemark(resultSet.getString("comment"));
                list.add(fee);
                // 达到批次大小时异步提交
                if (list.size() >= BATCH_SIZE) {
                    feeService.syncData(new ArrayList<>(list));
                    totalProcessed += list.size();
                    list.clear();
                    System.out.println("已处理: " + totalProcessed + " 条记录");
                }
            }
//                financeService.syncData(list);
            // 处理剩余记录
            if (!list.isEmpty()) {
                feeService.syncData(list);
                totalProcessed += list.size();
                System.out.println("总共处理: " + totalProcessed + " 条记录");
            }
        } catch (SQLException e) {
            System.err.println("读取表 btype 时出错！");
            e.printStackTrace();
        } finally {
            // 关闭资源
            closeResources(connection, preparedStatement, resultSet);
        }
    }


    //同步报销费用汇总
    @GetMapping(value = "/syncFee2022")
    public void syncFee2022() {
        final int BATCH_SIZE = 1000; // 每批处理1000条记录
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 获取连接
            connection = getConnectionHC2022() ;

            // SQL 查询语句
            /** 类型主类  根据数据观察  vcType是36 且 useType是1的表示是报销费用  vcType是4 类型是1的为收款    以上取大于0的*/
            String sql = "select btypeid,vchtype,total,[date],usedtype,etypeid,comment from dlya ";

            // 创建 PreparedStatement 对象
            preparedStatement = connection.prepareStatement(sql);

            // 执行查询
            resultSet = preparedStatement.executeQuery();
            // 处理结果集
            List<ProjectFee> list = new ArrayList<>();
            int totalProcessed = 0;
            while (resultSet.next()) {
                ProjectFee fee = new ProjectFee();
                String housekeeperProject = resultSet.getString("btypeid");
                fee.setHousekeeperProject(housekeeperProject);
                if (StringUtils.isEmpty(resultSet.getString("usedtype"))){//是空的默认设置一个值
                    fee.setUseType("999");
                }else {
                    fee.setUseType(resultSet.getString("usedtype"));
                }

                fee.setMainType(resultSet.getString("vchtype"));
                fee.setDatas("2022");
                fee.setTotal(resultSet.getBigDecimal("total"));
                fee.setSubmitDate(resultSet.getDate("date"));
                fee.setUserId(resultSet.getString("etypeid"));
                fee.setRemark(resultSet.getString("comment"));
                list.add(fee);
                // 达到批次大小时异步提交
                if (list.size() >= BATCH_SIZE) {
                    feeService.syncData(new ArrayList<>(list));
                    totalProcessed += list.size();
                    list.clear();
                    System.out.println("已处理: " + totalProcessed + " 条记录");
                }
            }
//                financeService.syncData(list);
            // 处理剩余记录
            if (!list.isEmpty()) {
                feeService.syncData(list);
                totalProcessed += list.size();
                System.out.println("总共处理: " + totalProcessed + " 条记录");
            }
        } catch (SQLException e) {
            System.err.println("读取表 btype 时出错！");
            e.printStackTrace();
        } finally {
            // 关闭资源
            closeResources(connection, preparedStatement, resultSet);
        }
    }



    //执行一次生成收款信息
    @GetMapping(value = "/createPay")
    public void  createPay() {
        List<Project> list = projectService.list();
        for (Project project : list) {
            projectService.createPay(project.getProjectNo());
        }
    }


    //同步管家婆用户
    @GetMapping(value = "/syncGjpUser")
    public void syncGjpUser() {
        final int BATCH_SIZE = 1000; // 每批处理1000条记录
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 获取连接
            connection = getConnectionHC2024() ;

            // SQL 查询语句
            String sql = "select etypeid,efullname,ecomment from employee ";

            // 创建 PreparedStatement 对象
            preparedStatement = connection.prepareStatement(sql);

            // 执行查询
            resultSet = preparedStatement.executeQuery();
            // 处理结果集
            List<ProjectGjpUser> list = new ArrayList<>();
            projectGjpUserService.deleteAll();//删除历史记录
            int totalProcessed = 0;
            while (resultSet.next()) {
                ProjectGjpUser user = new ProjectGjpUser();
                user.setUserId(resultSet.getString("etypeid"));
                user.setName(resultSet.getString("efullname"));
                // 达到批次大小时异步提交
                list.add(user);
                if (list.size() >= BATCH_SIZE) {
                    projectGjpUserService.syncData(new ArrayList<>(list));
                    totalProcessed += list.size();
                    list.clear();
                    System.out.println("已处理: " + totalProcessed + " 条记录");
                }
            }
//                financeService.syncData(list);
            // 处理剩余记录
            if (!list.isEmpty()) {
                projectGjpUserService.syncData(list);
                totalProcessed += list.size();
                System.out.println("总共处理: " + totalProcessed + " 条记录");
            }
        } catch (SQLException e) {
            System.err.println("读取表 btype 时出错！");
            e.printStackTrace();
        } finally {
            // 关闭资源
            closeResources(connection, preparedStatement, resultSet);
        }
    }


    //同步商品数据
    @GetMapping(value = "/syncGoods")
    public void syncGoods() {
        final int BATCH_SIZE = 1000; // 每批处理1000条记录
        int totalProcessed = 0;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 获取连接
            connection = getConnectionHC2024();
            // SQL 查询语句 deleted等于0 未删除
            String sql = "SELECT ptypeid,pusercode,pfullname,pname,pnamepy,deleted FROM ptype where deleted = 0";
            // 创建 PreparedStatement 对象
            preparedStatement = connection.prepareStatement(sql);
            // 执行查询
            resultSet = preparedStatement.executeQuery();
            // 处理结果集
            List<ProjectGoods> list = new ArrayList<>();
            while (resultSet.next()) {
                ProjectGoods goods = new ProjectGoods();
                String id = resultSet.getString("ptypeid");//商品
                String modal = resultSet.getString("pusercode");//商品型号
                String fullName = resultSet.getString("pfullname");//全称
                String name = resultSet.getString("pname");//简称
                goods.setId(id);
                goods.setModal(modal);
                goods.setFullName(fullName);
                goods.setName(name);
                goods.setParam(resultSet.getString("pnamepy"));//参数
                list.add(goods);
                // 输出数据
                System.out.println("goods " + goods);
                if (list.size() >= BATCH_SIZE) {
                    goodsService.syncData(new ArrayList<>(list));
                    totalProcessed += list.size();
                    list.clear();
                    System.out.println("已处理: " + totalProcessed + " 条记录");
                }
            }
            //更新或添加
//                goodsService.syncData(list);
            // 处理剩余记录
            if (!list.isEmpty()) {
                goodsService.syncData(list);
                totalProcessed += list.size();
                System.out.println("总共处理: " + totalProcessed + " 条记录");
            }
        } catch (SQLException e) {
            System.err.println("读取表 btype 时出错！");
            e.printStackTrace();
        } finally {
            // 关闭资源
            closeResources(connection, preparedStatement, resultSet);
        }
    }


    //同步商品数据
    @GetMapping(value = "/syncGoods2022")
    public void syncGoods2022() {
        final int BATCH_SIZE = 1000; // 每批处理1000条记录
        int totalProcessed = 0;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 获取连接
            connection = getConnectionHC2022();
            // SQL 查询语句 deleted等于0 未删除
            String sql = "SELECT ptypeid,pusercode,pfullname,pname,pnamepy,deleted FROM ptype where deleted = 0";
            // 创建 PreparedStatement 对象
            preparedStatement = connection.prepareStatement(sql);
            // 执行查询
            resultSet = preparedStatement.executeQuery();
            // 处理结果集
            List<ProjectGoods> list = new ArrayList<>();
            while (resultSet.next()) {
                ProjectGoods goods = new ProjectGoods();
                String id = resultSet.getString("ptypeid");//商品
                String modal = resultSet.getString("pusercode");//商品型号
                String fullName = resultSet.getString("pfullname");//全称
                String name = resultSet.getString("pname");//简称
                goods.setId(id);
                goods.setModal(modal);
                goods.setFullName(fullName);
                goods.setName(name);
                goods.setParam(resultSet.getString("pnamepy"));//参数
                list.add(goods);
                // 输出数据
                System.out.println("goods " + goods);
                if (list.size() >= BATCH_SIZE) {
                    goodsService.syncData(new ArrayList<>(list));
                    totalProcessed += list.size();
                    list.clear();
                    System.out.println("已处理: " + totalProcessed + " 条记录");
                }
            }
            //更新或添加
//                goodsService.syncData(list);
            // 处理剩余记录
            if (!list.isEmpty()) {
                goodsService.syncData(list);
                totalProcessed += list.size();
                System.out.println("总共处理: " + totalProcessed + " 条记录");
            }
        } catch (SQLException e) {
            System.err.println("读取表 btype 时出错！");
            e.printStackTrace();
        } finally {
            // 关闭资源
            closeResources(connection, preparedStatement, resultSet);
        }
    }

    //同步商品数据
    @GetMapping(value = "/syncGoodsXXWL")
    public void syncGoodsXXWL() {
        final int BATCH_SIZE = 1000; // 每批处理1000条记录
        int totalProcessed = 0;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 获取连接
            connection = getConnectionXXWL();
            // SQL 查询语句 deleted等于0 未删除
            String sql = "SELECT ptypeid,pusercode,pfullname,pname,pnamepy,deleted FROM ptype where deleted = 0";
            // 创建 PreparedStatement 对象
            preparedStatement = connection.prepareStatement(sql);
            // 执行查询
            resultSet = preparedStatement.executeQuery();
            // 处理结果集
            List<ProjectGoods> list = new ArrayList<>();
            while (resultSet.next()) {
                ProjectGoods goods = new ProjectGoods();
                String id = resultSet.getString("ptypeid");//商品
                String modal = resultSet.getString("pusercode");//商品型号
                String fullName = resultSet.getString("pfullname");//全称
                String name = resultSet.getString("pname");//简称
                goods.setId(id);
                goods.setModal(modal);
                goods.setFullName(fullName);
                goods.setName(name);
                goods.setParam(resultSet.getString("pnamepy"));//参数
                list.add(goods);
                // 输出数据
                System.out.println("goods " + goods);
                if (list.size() >= BATCH_SIZE) {
                    goodsService.syncData(new ArrayList<>(list));
                    totalProcessed += list.size();
                    list.clear();
                    System.out.println("已处理: " + totalProcessed + " 条记录");
                }
            }
            //更新或添加
//                goodsService.syncData(list);
            // 处理剩余记录
            if (!list.isEmpty()) {
                goodsService.syncData(list);
                totalProcessed += list.size();
                System.out.println("总共处理: " + totalProcessed + " 条记录");
            }
        } catch (SQLException e) {
            System.err.println("读取表 btype 时出错！");
            e.printStackTrace();
        } finally {
            // 关闭资源
            closeResources(connection, preparedStatement, resultSet);
        }
    }

    @GetMapping(value = "/syncGoodsXXWL2018")
    public void syncGoodsXXWL2018() {
        final int BATCH_SIZE = 1000; // 每批处理1000条记录
        int totalProcessed = 0;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 获取连接
            connection = getConnectionXXWL2018();
            // SQL 查询语句 deleted等于0 未删除
            String sql = "SELECT ptypeid,pusercode,pfullname,pname,pnamepy,deleted FROM ptype where deleted = 0";
            // 创建 PreparedStatement 对象
            preparedStatement = connection.prepareStatement(sql);
            // 执行查询
            resultSet = preparedStatement.executeQuery();
            // 处理结果集
            List<ProjectGoods> list = new ArrayList<>();
            while (resultSet.next()) {
                ProjectGoods goods = new ProjectGoods();
                String id = resultSet.getString("ptypeid");//商品
                String modal = resultSet.getString("pusercode");//商品型号
                String fullName = resultSet.getString("pfullname");//全称
                String name = resultSet.getString("pname");//简称
                goods.setId(id);
                goods.setModal(modal);
                goods.setFullName(fullName);
                goods.setName(name);
                goods.setParam(resultSet.getString("pnamepy"));//参数
                list.add(goods);
                // 输出数据
                System.out.println("goods " + goods);
                if (list.size() >= BATCH_SIZE) {
                    goodsService.syncData(new ArrayList<>(list));
                    totalProcessed += list.size();
                    list.clear();
                    System.out.println("已处理: " + totalProcessed + " 条记录");
                }
            }
            //更新或添加
//                goodsService.syncData(list);
            // 处理剩余记录
            if (!list.isEmpty()) {
                goodsService.syncData(list);
                totalProcessed += list.size();
                System.out.println("总共处理: " + totalProcessed + " 条记录");
            }
        } catch (SQLException e) {
            System.err.println("读取表 btype 时出错！");
            e.printStackTrace();
        } finally {
            // 关闭资源
            closeResources(connection, preparedStatement, resultSet);
        }
    }




    //同步入库数据
    @GetMapping(value = "/syncStoreDataHC2024")
    public void syncStoreDataHC2024() {
        int totalProcessed = 0;
        final int BATCH_SIZE = 1000;
        List<ProjectStore> list = new ArrayList<>();

        try (Connection dbConn = getConnectionHC2024();
             Statement stmt = dbConn.createStatement();
             ResultSet resultSet = stmt.executeQuery("SELECT Vchcode, PtypeId,Qty,price,[date] FROM dlybuy")) {
            projectStoreMapper.deleteAllHC2024();
            while (resultSet.next()) {
                ProjectStore store = new ProjectStore();
                store.setGoodsId(resultSet.getString("ptypeid"));
                store.setPrice(resultSet.getBigDecimal("price"));
                store.setAmount(resultSet.getInt("Qty"));
                store.setStorageDate(resultSet.getDate("date"));
                store.setDatas("HC2024");
                list.add(store);
                System.out.println("store " + store);

                if (list.size() >= BATCH_SIZE) {
                    projectStoreService.syncData(new ArrayList<>(list));
                    totalProcessed += list.size();
                    list.clear();
                    System.out.println("数据已处理: " + totalProcessed + " 条记录");
                }
            }
            // 处理剩余记录
            if (!list.isEmpty()) {
                projectStoreService.syncData(list);
                totalProcessed += list.size();
            }
            System.out.println("数据同步完成，总处理记录数: " + totalProcessed);
        } catch (SQLException e) {
            System.out.println( "数据库操作异常：" + e.getMessage());
            e.printStackTrace();
        }
    }
    //同步入库数据
    @GetMapping(value = "/syncStoreDataHC2022")
    public void syncStoreDataHC2022() {
        int totalProcessed = 0;
        final int BATCH_SIZE = 1000;
        List<ProjectStore> list = new ArrayList<>();

        try (Connection dbConn = getConnectionHC2022();
             Statement stmt = dbConn.createStatement();
             ResultSet resultSet = stmt.executeQuery("SELECT Vchcode, PtypeId,Qty,price,[date] FROM dlybuy")) {
            projectStoreMapper.deleteAllHC2022();
            while (resultSet.next()) {
                ProjectStore store = new ProjectStore();
                store.setGoodsId(resultSet.getString("ptypeid"));
                store.setPrice(resultSet.getBigDecimal("price"));
                store.setAmount(resultSet.getInt("Qty"));
                store.setStorageDate(resultSet.getDate("date"));
                store.setDatas("HC2022");
                list.add(store);
                System.out.println("store " + store);

                if (list.size() >= BATCH_SIZE) {
                    projectStoreService.syncData(new ArrayList<>(list));
                    totalProcessed += list.size();
                    list.clear();
                    System.out.println("数据已处理: " + totalProcessed + " 条记录");
                }
            }
            // 处理剩余记录
            if (!list.isEmpty()) {
                projectStoreService.syncData(list);
                totalProcessed += list.size();
            }
            System.out.println("数据同步完成，总处理记录数: " + totalProcessed);
        } catch (SQLException e) {
            System.out.println( "数据库操作异常：" + e.getMessage());
            e.printStackTrace();
        }
    }
    //同步入库数据
    @GetMapping(value = "/syncStoreDataHCXXWL")
    public void syncStoreDataHCXXWL() {
        int totalProcessed = 0;
        final int BATCH_SIZE = 1000;
        List<ProjectStore> list = new ArrayList<>();

        try (Connection dbConn = getConnectionXXWL();
             Statement stmt = dbConn.createStatement();
             ResultSet resultSet = stmt.executeQuery("SELECT Vchcode, PtypeId,Qty,price,[date] FROM dlybuy")) {
            projectStoreMapper.deleteAllXXWL();
            while (resultSet.next()) {
                ProjectStore store = new ProjectStore();
                store.setGoodsId(resultSet.getString("ptypeid"));
                store.setPrice(resultSet.getBigDecimal("price"));
                store.setAmount(resultSet.getInt("Qty"));
                store.setStorageDate(resultSet.getDate("date"));
                store.setDatas("XXWL");
                list.add(store);
                System.out.println("store " + store);

                if (list.size() >= BATCH_SIZE) {
                    projectStoreService.syncData(new ArrayList<>(list));
                    totalProcessed += list.size();
                    list.clear();
                    System.out.println("数据已处理: " + totalProcessed + " 条记录");
                }
            }
            // 处理剩余记录
            if (!list.isEmpty()) {
                projectStoreService.syncData(list);
                totalProcessed += list.size();
            }
            System.out.println("数据同步完成，总处理记录数: " + totalProcessed);
        } catch (SQLException e) {
            System.out.println( "数据库操作异常：" + e.getMessage());
            e.printStackTrace();
        }
    }
    //同步入库数据
    @GetMapping(value = "/syncStoreDataHCXXWL2018")
    public void syncStoreDataHCXXWL2018() {
        int totalProcessed = 0;
        final int BATCH_SIZE = 1000;
        List<ProjectStore> list = new ArrayList<>();

        try (Connection dbConn = getConnectionXXWL2018();
             Statement stmt = dbConn.createStatement();
             ResultSet resultSet = stmt.executeQuery("SELECT Vchcode, PtypeId,Qty,price,[date] FROM dlybuy")) {
            projectStoreMapper.deleteAllXXWL2018();
            while (resultSet.next()) {
                ProjectStore store = new ProjectStore();
                store.setGoodsId(resultSet.getString("ptypeid"));
                store.setPrice(resultSet.getBigDecimal("price"));
                store.setAmount(resultSet.getInt("Qty"));
                store.setStorageDate(resultSet.getDate("date"));
                store.setDatas("XXWL2018");
                list.add(store);
                System.out.println("store " + store);

                if (list.size() >= BATCH_SIZE) {
                    projectStoreService.syncData(new ArrayList<>(list));
                    totalProcessed += list.size();
                    list.clear();
                    System.out.println("数据已处理: " + totalProcessed + " 条记录");
                }
            }
            // 处理剩余记录
            if (!list.isEmpty()) {
                projectStoreService.syncData(list);
                totalProcessed += list.size();
            }
            System.out.println("数据同步完成，总处理记录数: " + totalProcessed);
        } catch (SQLException e) {
            System.out.println( "数据库操作异常：" + e.getMessage());
            e.printStackTrace();
        }
    }


    @GetMapping(value = "/syncSaleHC2024")
    public void syncSaleHC2024() {
        final int BATCH_SIZE = 1000;
        int totalProcessed = 0;
        List<ProjectSale> list = new ArrayList<>(BATCH_SIZE);

        Connection dbConn = null;
        Statement stmt = null;
        ResultSet resultSet = null;

        try {
            // 获取数据库连接（根据年份选择不同连接）
            dbConn = getConnectionHC2024();


            // 定义SQL语句
            String sql = "SELECT Vchcode, PtypeId, Qty, price, btypeid, costprice, costtotal, total, [date],InvoceTotal,DlyOrder FROM dlysale";

            // 创建执行SQL对象
            stmt = dbConn.createStatement();

            // 执行SQL查询
            resultSet = stmt.executeQuery(sql);

            projectSaleMapper.deleteAllHC2024();

            while (resultSet.next()) {
                ProjectSale sale = new ProjectSale();
                sale.setGoodsId(resultSet.getString("ptypeid"));
                sale.setHousekeeperProject(resultSet.getString("btypeid"));
                sale.setPrice(resultSet.getBigDecimal("price"));
                sale.setSaleDate(resultSet.getDate("date"));
                sale.setCostPrice(resultSet.getBigDecimal("costprice"));
                sale.setAmount(resultSet.getInt("Qty"));
                sale.setDatas("HC2024");
                sale.setInvoiceAmount(resultSet.getBigDecimal("InvoceTotal"));
                sale.setCode(String.valueOf(resultSet.getInt("Vchcode")));
                sale.setOrders(String.valueOf(resultSet.getString("DlyOrder")));
                // 处理金额计算
                BigDecimal costTotal = resultSet.getBigDecimal("costtotal");
                BigDecimal total = resultSet.getBigDecimal("total");
                sale.setCostTotal(costTotal);
                sale.setTotal(total);
                sale.setProfit(total.abs().subtract(costTotal.abs()));
                // 输出数据详情
                System.out.println("成本合计（正数）: " + sale.getCostTotal());
                System.out.println("销售总金额（正数）: " + sale.getTotal());
                System.out.println("毛利: " + sale.getProfit());

                list.add(sale);
                totalProcessed++;

                // 达到批次大小时提交
                if (list.size() >= BATCH_SIZE) {
                    projectSaleService.syncData(new ArrayList<>(list));
                    System.out.println( "数据已处理: " + totalProcessed + " 条记录");
                    list.clear();
                }
            }

            // 处理剩余记录
            if (!list.isEmpty()) {
                projectSaleService.syncData(list);
                System.out.println("数据同步完成，总共处理: " + totalProcessed + " 条记录");
            }
        } catch (SQLException e) {
            System.out.println( "数据库操作异常：" + e.getMessage());
            e.printStackTrace();
        } finally {
            // 关闭资源
            closeResources(dbConn, stmt, resultSet);
        }
    }

    @GetMapping(value = "/syncSaleHC2022")
    public void syncSaleHC2022() {
        final int BATCH_SIZE = 1000;
        int totalProcessed = 0;
        List<ProjectSale> list = new ArrayList<>(BATCH_SIZE);

        Connection dbConn = null;
        Statement stmt = null;
        ResultSet resultSet = null;

        try {
            // 获取数据库连接（根据年份选择不同连接）
            dbConn = getConnectionHC2022();

            // 定义SQL语句
            String sql = "SELECT Vchcode, PtypeId, Qty, price, btypeid, costprice, costtotal, total, [date],InvoceTotal,DlyOrder FROM dlysale";

            // 创建执行SQL对象
            stmt = dbConn.createStatement();

            // 执行SQL查询
            resultSet = stmt.executeQuery(sql);

            projectSaleMapper.deleteAllHC2022();

            while (resultSet.next()) {
                ProjectSale sale = new ProjectSale();
                sale.setGoodsId(resultSet.getString("ptypeid"));
                sale.setHousekeeperProject(resultSet.getString("btypeid"));
                sale.setPrice(resultSet.getBigDecimal("price"));
                sale.setSaleDate(resultSet.getDate("date"));
                sale.setCostPrice(resultSet.getBigDecimal("costprice"));
                sale.setAmount(resultSet.getInt("Qty"));
                sale.setDatas("HC2022");
                sale.setInvoiceAmount(resultSet.getBigDecimal("InvoceTotal"));
                sale.setCode(String.valueOf(resultSet.getInt("Vchcode")));
                sale.setOrders(String.valueOf(resultSet.getString("DlyOrder")));
                // 处理金额计算
                BigDecimal costTotal = resultSet.getBigDecimal("costtotal");
                BigDecimal total = resultSet.getBigDecimal("total");
                sale.setCostTotal(costTotal);
                sale.setTotal(total);
                sale.setProfit(total.abs().subtract(costTotal.abs()));

                // 输出数据详情
                System.out.println("成本合计（正数）: " + sale.getCostTotal());
                System.out.println("销售总金额（正数）: " + sale.getTotal());
                System.out.println("毛利: " + sale.getProfit());

                list.add(sale);
                totalProcessed++;

                // 达到批次大小时提交
                if (list.size() >= BATCH_SIZE) {
                    projectSaleService.syncData(new ArrayList<>(list));
                    System.out.println("数据已处理: " + totalProcessed + " 条记录");
                    list.clear();
                }
            }

            // 处理剩余记录
            if (!list.isEmpty()) {
                projectSaleService.syncData(list);
                System.out.println("数据同步完成，总共处理: " + totalProcessed + " 条记录");
            }
        } catch (SQLException e) {
            System.out.println("数据库操作异常：" + e.getMessage());
            e.printStackTrace();
        } finally {
            // 关闭资源
            closeResources(dbConn, stmt, resultSet);
        }
    }

    @GetMapping(value = "/syncSaleHCXXWL2018")
    public void syncSaleHCXXWL2018() {
        final int BATCH_SIZE = 1000;
        int totalProcessed = 0;
        List<ProjectSale> list = new ArrayList<>(BATCH_SIZE);

        Connection dbConn = null;
        Statement stmt = null;
        ResultSet resultSet = null;

        try {
            // 获取数据库连接（根据年份选择不同连接）
            dbConn = getConnectionXXWL2018();

            // 定义SQL语句
            String sql = "SELECT Vchcode, PtypeId, Qty, price, btypeid, costprice, costtotal, total, [date],InvoceTotal,DlyOrder FROM dlysale";

            // 创建执行SQL对象
            stmt = dbConn.createStatement();

            // 执行SQL查询
            resultSet = stmt.executeQuery(sql);

            projectSaleMapper.deleteAllXXWL2018();

            while (resultSet.next()) {
                ProjectSale sale = new ProjectSale();
                sale.setGoodsId(resultSet.getString("ptypeid"));
                sale.setHousekeeperProject(resultSet.getString("btypeid"));
                sale.setPrice(resultSet.getBigDecimal("price"));
                sale.setSaleDate(resultSet.getDate("date"));
                sale.setCostPrice(resultSet.getBigDecimal("costprice"));
                sale.setAmount(resultSet.getInt("Qty"));
                sale.setDatas("XXWL2018");
                sale.setInvoiceAmount(resultSet.getBigDecimal("InvoceTotal"));
                sale.setCode(String.valueOf(resultSet.getInt("Vchcode")));
                sale.setOrders(String.valueOf(resultSet.getString("DlyOrder")));
                // 处理金额计算
                BigDecimal costTotal = resultSet.getBigDecimal("costtotal");
                BigDecimal total = resultSet.getBigDecimal("total");
                sale.setCostTotal(costTotal);
                sale.setTotal(total);
                sale.setProfit(total.abs().subtract(costTotal.abs()));

                // 输出数据详情
                System.out.println("成本合计（正数）: " + sale.getCostTotal());
                System.out.println("销售总金额（正数）: " + sale.getTotal());
                System.out.println("毛利: " + sale.getProfit());

                list.add(sale);
                totalProcessed++;

                // 达到批次大小时提交
                if (list.size() >= BATCH_SIZE) {
                    projectSaleService.syncData(new ArrayList<>(list));
                    System.out.println("数据已处理: " + totalProcessed + " 条记录");
                    list.clear();
                }
            }

            // 处理剩余记录
            if (!list.isEmpty()) {
                projectSaleService.syncData(list);
                System.out.println("数据同步完成，总共处理: " + totalProcessed + " 条记录");
            }
        } catch (SQLException e) {
            System.out.println("数据库操作异常：" + e.getMessage());
            e.printStackTrace();
        } finally {
            // 关闭资源
            closeResources(dbConn, stmt, resultSet);
        }
    }

    @GetMapping(value = "/syncSaleHCXXWL")
    public void syncSaleHCXXWL() {
        final int BATCH_SIZE = 1000;
        int totalProcessed = 0;
        List<ProjectSale> list = new ArrayList<>(BATCH_SIZE);

        Connection dbConn = null;
        Statement stmt = null;
        ResultSet resultSet = null;

        try {
            // 获取数据库连接（根据年份选择不同连接）
            dbConn = getConnectionXXWL();

            // 定义SQL语句
            String sql = "SELECT Vchcode, PtypeId, Qty, price, btypeid, costprice, costtotal, total, [date],InvoceTotal,DlyOrder FROM dlysale";

            // 创建执行SQL对象
            stmt = dbConn.createStatement();

            // 执行SQL查询
            resultSet = stmt.executeQuery(sql);

            projectSaleMapper.deleteAllXXWL();

            while (resultSet.next()) {
                ProjectSale sale = new ProjectSale();
                sale.setGoodsId(resultSet.getString("ptypeid"));
                sale.setHousekeeperProject(resultSet.getString("btypeid"));
                sale.setPrice(resultSet.getBigDecimal("price"));
                sale.setSaleDate(resultSet.getDate("date"));

                BigDecimal costPrice = resultSet.getBigDecimal("costprice");

// 截断超出范围的值
                BigDecimal maxValue = new BigDecimal("99999999.99");
                BigDecimal minValue = new BigDecimal("0.00");

                if (costPrice.compareTo(maxValue) > 0) {
                    costPrice = maxValue;
                } else if (costPrice.compareTo(minValue) < 0) {
                    costPrice = minValue;
                }

                sale.setAmount(resultSet.getInt("Qty"));
                sale.setDatas("XXWL");
                sale.setInvoiceAmount(resultSet.getBigDecimal("InvoceTotal"));
                sale.setCode(String.valueOf(resultSet.getInt("Vchcode")));
                sale.setOrders(String.valueOf(resultSet.getString("DlyOrder")));
                // 处理金额计算
                BigDecimal costTotal = resultSet.getBigDecimal("costtotal");
                BigDecimal total = resultSet.getBigDecimal("total");
                sale.setCostTotal(costTotal);
                sale.setTotal(total);
                sale.setProfit(total.abs().subtract(costTotal.abs()));

                // 校验并截断超出范围的值
                if (costPrice.compareTo(MAX_VALUE) > 0) {
                    System.out.println("Cost price exceeds maximum value. Truncating to: " + MAX_VALUE);
                    costPrice = MAX_VALUE; // 截断为最大值
                } else if (costPrice.compareTo(MIN_VALUE) < 0) {
                    System.out.println("Cost price is below minimum value. Truncating to: " + MIN_VALUE);
                    costPrice = MIN_VALUE; // 截断为最小值
                }
                sale.setCostPrice(costPrice);
                // 输出数据详情
                System.out.println("成本合计（正数）: " + sale.getCostTotal());
                System.out.println("销售总金额（正数）: " + sale.getTotal());
                System.out.println("毛利: " + sale.getProfit());

                list.add(sale);
                totalProcessed++;

                // 达到批次大小时提交
                if (list.size() >= BATCH_SIZE) {
                    projectSaleService.syncData(new ArrayList<>(list));
                    System.out.println("数据已处理: " + totalProcessed + " 条记录");
                    list.clear();
                }
            }

            // 处理剩余记录
            if (!list.isEmpty()) {
                projectSaleService.syncData(list);
                System.out.println("数据同步完成，总共处理: " + totalProcessed + " 条记录");
            }
        } catch (SQLException e) {
            System.out.println("数据库操作异常：" + e.getMessage());
            e.printStackTrace();
        } finally {
            // 关闭资源
            closeResources(dbConn, stmt, resultSet);
        }
    }

    //
    @GetMapping(value = "/sqlserver")
    public void testSqlserver() throws SQLException {
        //定义数据库用户
        String userName = "sa";
        //定义数据库密码
        String userPwd = "NBhcit-2020";
        //定义数据库连接对象
        Connection dbConn = null;
        //定义命令对象
        Statement stmt = null;
        try {
            String dbURL = "jdbc:sqlserver://172.30.30.30:1433;databaseName=HC2024;";
            dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
            System.out.println("连接数据库成功");
            //4.定义SQL语句
            String sql = "SELECT TOP 10 * FROM btype;";
            //5.创建一个执行sql对象
            stmt = dbConn.createStatement();
            //6.执行SQL语句命令
            ResultSet resultSet = stmt.executeQuery(sql);
            //7.处理结果，并获取数据
            int flag = 0;
            while (resultSet.next() && flag <= 10) {
                String name = resultSet.getString("bfullname");
                System.out.println("namename" + name);
                flag++;
            }
            //8.关闭连接
            resultSet.close();
            dbConn.close();
            stmt.close();
        } catch (SQLException e) {
            //数据库的处理异常的方法
            System.out.println("========" + e.getMessage());
            e.printStackTrace();
        } finally {
            if (dbConn != null) {
                try {
                    dbConn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 获取数据库连接
     *
     * @return Connection 对象
     */
    public Connection getConnectionMysql() {
        Connection connection = null;
        try {
            // 加载 MySQL 驱动程序
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            String URL = "jdbc:mysql://172.30.30.141:3306/haochengjixiao";
            String USER = "jixiao";
            String PASSWORD = "haocheng0509";
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (ClassNotFoundException e) {
            System.err.println("MySQL 驱动未找到！");
            e.printStackTrace();
        } catch (SQLException e) {
            System.err.println("数据库连接失败！");
            e.printStackTrace();
        }
        return connection;
    }


    //同步出库数据
    @GetMapping(value = "/syncSaleMysql")
    public void syncSaleMysql() {
        final int BATCH_SIZE = 1000;
        int totalProcessed = 0;
        List<ProjectSale> list = new ArrayList<>(BATCH_SIZE);

        Connection dbConn = null;
        Statement stmt = null;
        ResultSet resultSet = null;

        try {
            // 获取数据库连接（根据年份选择不同连接）
            dbConn = getConnectionMysql();

            // 定义SQL语句
            String sql = "SELECT Vchcode, PtypeId, Qty, price, btypeid, costprice, costtotal, total, date FROM dlysale";

            // 创建执行SQL对象
            stmt = dbConn.createStatement();

            // 执行SQL查询
            resultSet = stmt.executeQuery(sql);

//                projectSaleService.deleteAll();
            while (resultSet.next()) {
                ProjectSale sale = new ProjectSale();
                sale.setGoodsId(resultSet.getString("ptypeid"));
                sale.setHousekeeperProject(resultSet.getString("btypeid"));
                sale.setPrice(resultSet.getBigDecimal("price"));
                sale.setSaleDate(resultSet.getDate("date"));
                sale.setCostPrice(resultSet.getBigDecimal("costprice"));
                sale.setAmount(resultSet.getInt("Qty"));

                // 处理金额计算
                BigDecimal costTotal = resultSet.getBigDecimal("costtotal");
                BigDecimal total = resultSet.getBigDecimal("total");
                sale.setCostTotal(costTotal);
                sale.setTotal(total);
                sale.setProfit(total.abs().subtract(costTotal.abs()));

                // 输出数据详情
                System.out.println("成本合计（正数）: " + sale.getCostTotal());
                System.out.println("销售总金额（正数）: " + sale.getTotal());
                System.out.println("毛利: " + sale.getProfit());

                list.add(sale);
                totalProcessed++;

                // 达到批次大小时提交
                if (list.size() >= BATCH_SIZE) {
                    projectSaleService.syncData(new ArrayList<>(list));
                    System.out.println( "数据已处理: " + totalProcessed + " 条记录");
                    list.clear();
                }
            }

            // 处理剩余记录
            if (!list.isEmpty()) {
                projectSaleService.syncData(list);
                System.out.println( "数据同步完成，总共处理: " + totalProcessed + " 条记录");
            }
        } catch (SQLException e) {
            System.out.println( "数据库操作异常：" + e.getMessage());
            e.printStackTrace();
        } finally {
            // 关闭资源
            closeResources(dbConn, stmt, resultSet);
        }
    }

    //同步入库数据
    @GetMapping(value = "/syncStoreMysql")
    public void syncStoreMysql() {
        int totalProcessed = 0;
        final int BATCH_SIZE = 1000;
        List<ProjectStore> list = new ArrayList<>();

        try (Connection dbConn = getConnectionMysql();
             Statement stmt = dbConn.createStatement();
             ResultSet resultSet = stmt.executeQuery("SELECT Vchcode, PtypeId,Qty,price,date FROM dlybuy")) {
            projectStoreService.deleteAll();
            while (resultSet.next()) {
                ProjectStore store = new ProjectStore();
                store.setGoodsId(resultSet.getString("ptypeid"));
                store.setPrice(resultSet.getBigDecimal("price"));
                store.setAmount(resultSet.getInt("Qty"));
                store.setStorageDate(resultSet.getDate("date"));

                list.add(store);
                System.out.println("store " + store);

                if (list.size() >= BATCH_SIZE) {
                    projectStoreService.syncData(new ArrayList<>(list));
                    totalProcessed += list.size();
                    list.clear();
                    System.out.println("数据已处理: " + totalProcessed + " 条记录");
                }
            }
            // 处理剩余记录
            if (!list.isEmpty()) {
                projectStoreService.syncData(list);
                totalProcessed += list.size();
            }
            System.out.println( "数据同步完成，总处理记录数: " + totalProcessed);
        } catch (SQLException e) {
            System.out.println("数据库操作异常：" + e.getMessage());
            e.printStackTrace();
        }
    }


    /**
     * 获取数据库连接 连接HC2022
     *
     * @return 数据库连接对象
     * @throws SQLException 如果连接失败
     */
    private static Connection getConnectionHC2022() throws SQLException {
        try {
            System.out.println("正在连接数据库...");
            Connection connection = DriverManager.getConnection(DB_URL_HC2022, USER_NAME, USER_PWD);
            System.out.println("连接数据库成功！");
            return connection;
        } catch (SQLException e) {
            System.err.println("数据库连接失败：" + e.getMessage());
            throw e;
        }
    }

    /**
     * 获取数据库连接 连接HC2024
     *
     * @return 数据库连接对象
     * @throws SQLException 如果连接失败
     */
    private static Connection getConnectionHC2024() throws SQLException {
        try {
            System.out.println("正在连接数据库...");
            Connection connection = DriverManager.getConnection(DB_URL_HC2024, USER_NAME, USER_PWD);
            System.out.println("连接数据库成功！");
            return connection;
        } catch (SQLException e) {
            System.err.println("数据库连接失败：" + e.getMessage());
            throw e;
        }
    }


    private static Connection getConnectionXXWL() throws SQLException {
        try {
            System.out.println("正在连接数据库...");
            Connection connection = DriverManager.getConnection(DB_URL_XXWL, USER_NAME, USER_PWD);
            System.out.println("连接数据库成功！");
            return connection;
        } catch (SQLException e) {
            System.err.println("数据库连接失败：" + e.getMessage());
            throw e;
        }
    }

    private static Connection getConnectionXXWL2018() throws SQLException {
        try {
            System.out.println("正在连接数据库...");
            Connection connection = DriverManager.getConnection(DB_URL_XXWL2018, USER_NAME, USER_PWD);
            System.out.println("连接数据库成功！");
            return connection;
        } catch (SQLException e) {
            System.err.println("数据库连接失败：" + e.getMessage());
            throw e;
        }
    }



    /**
     * 关闭数据库资源
     *
     * @param conn 数据库连接
     * @param stmt 命令对象
     * @param rs   结果集
     */
    private static void closeResources(Connection conn, Statement stmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}





